package com.example.tongyao.system.controller;

import com.example.tongyao.utils.PoiUtils;
import io.swagger.annotations.Api;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Range;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xwpf.model.XWPFHeaderFooterPolicy;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFHeader;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.xmlbeans.impl.xb.xmlschema.SpaceAttribute;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTP;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTSectPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STHdrFtr;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;


import com.aspose.words.Shape;
import com.aspose.words.*;



/**
 * tongyao >>> 【com.example.tongyao.system.controller】
 * 导出文件
 *
 * @author: tongyao
 * @since: 2021-4-23 20:13
 */
@RestController
@Api(tags = "系统 - 导出文件")
@RequestMapping("/system/sys-export-file")
public class ExportController {

    //用于导出本地并打开文件防止报headless异常代码
    /*static {
        System.setProperty("java.awt.headless", "false");
    }*/
    
    //导出表格
    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
        /*
         * 关于poi注意事项：如果是xls，使用HSSFWorkbook；如果是xlsx，使用XSSFWorkbook
         * */

         /*设置背景颜色*/
        /*cellBoldStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HSSFPalette palette = wb.getCustomPalette();
        cellBoldStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
        palette.setColorAtIndex(IndexedColors.LIME.getIndex(), (byte) 204, (byte) 204, (byte) 255);*/

        try {
            HSSFWorkbook wb = new HSSFWorkbook();

            HSSFSheet sheet = wb.createSheet("sheet");
            //sheet.setColumnWidth(i, (int)(title.getBytes().length * 1.2d * 256 > 12 * 256 ? title.getBytes().length * 1.2d * 256 : 12 * 256));
            //256为单个字符所占的宽度
            //1.2d表示比实际宽度大20%
            //12*256表示最低宽度为12个字符
            //设置列宽
            sheet.setColumnWidth(1,10000);

            HSSFRow row1 = sheet.createRow(0);
            Cell row1_title1 = row1.createCell(0);
            row1_title1.setCellValue("姓名");

            Cell row1_title2 = row1.createCell(1);
            row1_title2.setCellValue("年龄");


            Map map1 = new HashMap();
            map1.put("name","张三");
            map1.put("age","12");

            Map map2 = new HashMap();
            map2.put("name","李四");
            map2.put("age","42");

            Map map3 = new HashMap();
            map3.put("name","王五");
            map3.put("age","21");

            List<Map<String,String>> list = new ArrayList<>();
            list.add(map1);
            list.add(map2);
            list.add(map3);

            for (int i = 0;i<list.size();i++){
                HSSFRow row = sheet.createRow((i+1));
                Cell row_title1 = row.createCell(0);
                row_title1.setCellValue(list.get(i).get("name"));

                Cell row_title2 = row.createCell(1);
                row_title2.setCellValue(list.get(i).get("age"));
            }

            /********************本地下载***********************/

            /*String fileName = "D:\\exportFrom\\"+new Date().getTime() +".xls";

            FileOutputStream fileOutputStream = new FileOutputStream(fileName);
            // 写入excel文件
            wb.write(fileOutputStream);
            fileOutputStream.close();
            System.out.println("----Excle文件已生成------");

            java.awt.Desktop.getDesktop().open(new File(fileName));

            //导出后自动停止应用
            System.exit(0);*/

            /********************浏览器下载文件******************/
            // 输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();

            String fileName = "导出的示例文件.xls";
            // 设置文件头
            response.setHeader("Content-Disposition",
                    "attchement;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));

            response.setContentType("application/msexcel");
            wb.write(output);
            wb.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
        }


        /**以下是一个复杂的示例*/
        //读取模版路径
        /*String templatePath = System.getProperty("user.dir")+"\\src\\main\\resources\\file_template\\export_template.xls";
        try {
            //找到模版文件路径
            HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(templatePath));

            // 根据页面index 获取sheet页
            HSSFSheet sheet = wb.getSheet("Sheet1");


            //初始化

            //声明顶部几行的字体信息
            HSSFFont fontTitle = wb.createFont();
            //设置字体加粗
            fontTitle.setBold(true);

            *//*****************************************************样式1开始*//*
            //声明顶部几行的样式信息
            HSSFCellStyle cellBoldStyle = wb.createCellStyle();

            //设置垂直居中
            cellBoldStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置水平居中
            cellBoldStyle.setAlignment(HorizontalAlignment.CENTER);
            //使用字体加粗
            cellBoldStyle.setFont(fontTitle);
            //设置以上下左右边框
            cellBoldStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellBoldStyle.setBorderLeft(BorderStyle.THIN);//左边框
            cellBoldStyle.setBorderTop(BorderStyle.THIN);//上边框
            cellBoldStyle.setBorderRight(BorderStyle.THIN);//右边框
            *//*****************************************************样式1结束*//*


            *//*****************************************************样式2开始*//*
            //声明顶部几行的样式信息
            HSSFCellStyle cellBoldStyleNoBorder = wb.createCellStyle();

            //设置垂直居中
            //cellBoldStyleNoBorder.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置水平居中
            //cellBoldStyleNoBorder.setAlignment(HorizontalAlignment.CENTER);
            //使用字体加粗
            cellBoldStyleNoBorder.setFont(fontTitle);
            *//*****************************************************样式2结束*//*


            *//*****************************************************样式3开始*//*
            HSSFCellStyle cellAutoRowStyle = wb.createCellStyle();
            //设置自动换行
            cellAutoRowStyle.setWrapText(true);
            //设置垂直居中
            cellAutoRowStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            //设置以上下左右边框
            cellAutoRowStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellAutoRowStyle.setBorderLeft(BorderStyle.THIN);//左边框
            cellAutoRowStyle.setBorderTop(BorderStyle.THIN);//上边框
            cellAutoRowStyle.setBorderRight(BorderStyle.THIN);//右边框
            *//*****************************************************样式3结束*//*


            *//*****************************************************样式4开始*//*
            //声明顶部几行的样式信息
            HSSFCellStyle cellStyle = wb.createCellStyle();

            //设置垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置以上下左右边框
            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyle.setBorderTop(BorderStyle.THIN);//上边框
            cellStyle.setBorderRight(BorderStyle.THIN);//右边框
            *//*****************************************************样式4结束*//*


            //设置第一行表头信息
            HSSFRow rowTitle1 = sheet.createRow(1);
            Cell rowTitle1_cellTitle1 = rowTitle1.createCell(0);
            rowTitle1_cellTitle1.setCellValue("检查时间");
            rowTitle1_cellTitle1.setCellStyle(cellBoldStyle);

            Cell rowTitle1_cellTitle2 = rowTitle1.createCell(1);
            rowTitle1_cellTitle2.setCellValue(RWInfo.get("xcrq").toString());
            rowTitle1_cellTitle2.setCellStyle(cellStyle);
            //修补合并后边框不完整的情况
            rowTitle1.createCell(2).setCellStyle(cellStyle);
            rowTitle1.createCell(3).setCellStyle(cellStyle);
            rowTitle1.createCell(4).setCellStyle(cellStyle);
            rowTitle1.createCell(5).setCellStyle(cellStyle);
            rowTitle1.createCell(6).setCellStyle(cellStyle);
            rowTitle1.createCell(7).setCellStyle(cellStyle);
            rowTitle1.createCell(8).setCellStyle(cellStyle);
            rowTitle1.createCell(9).setCellStyle(cellStyle);
            rowTitle1.createCell(10).setCellStyle(cellStyle);

            //设置第五行表头信息
            HSSFRow rowTitle4 = sheet.createRow(4);
            Cell rowTitle4_cellTitle1 = rowTitle4.createCell(0);
            rowTitle4_cellTitle1.setCellValue("检查类型");
            rowTitle4_cellTitle1.setCellStyle(cellBoldStyle);

            Cell rowTitle4__rowTitle2 = rowTitle4.createCell(1);
            rowTitle4__rowTitle2.setCellValue(RWInfo.get("xclx").toString());
            rowTitle4__rowTitle2.setCellStyle(cellStyle);
            //修补合并后边框不完整的情况
            rowTitle4.createCell(2).setCellStyle(cellStyle);
            rowTitle4.createCell(3).setCellStyle(cellStyle);
            rowTitle4.createCell(4).setCellStyle(cellStyle);
            rowTitle4.createCell(5).setCellStyle(cellStyle);
            rowTitle4.createCell(6).setCellStyle(cellStyle);
            rowTitle4.createCell(7).setCellStyle(cellStyle);
            rowTitle4.createCell(8).setCellStyle(cellStyle);
            rowTitle4.createCell(9).setCellStyle(cellStyle);
            rowTitle4.createCell(10).setCellStyle(cellStyle);


            //设置第六行表头信息
            HSSFRow rowTitle5 = sheet.createRow(5);
            Cell rowTitle5_cellTitle1 = rowTitle5.createCell(0);
            rowTitle5_cellTitle1.setCellValue("检查对象");
            rowTitle5_cellTitle1.setCellStyle(cellBoldStyle);

            Cell rowTitle5__rowTitle2 = rowTitle5.createCell(1);
            rowTitle5__rowTitle2.setCellValue("潮河主坝1");
            rowTitle5__rowTitle2.setCellStyle(cellStyle);
            //修补合并后边框不完整的情况
            rowTitle5.createCell(2).setCellStyle(cellStyle);
            rowTitle5.createCell(3).setCellStyle(cellStyle);
            rowTitle5.createCell(4).setCellStyle(cellStyle);
            rowTitle5.createCell(5).setCellStyle(cellStyle);
            rowTitle5.createCell(6).setCellStyle(cellStyle);
            rowTitle5.createCell(7).setCellStyle(cellStyle);
            rowTitle5.createCell(8).setCellStyle(cellStyle);
            rowTitle5.createCell(9).setCellStyle(cellStyle);
            rowTitle5.createCell(10).setCellStyle(cellStyle);

            //设置第七行表头信息
            HSSFRow rowTitle6 = sheet.createRow(6);
            Cell rowTitle6_cellTitle1 = rowTitle6.createCell(0);
            rowTitle6_cellTitle1.setCellValue("巡视对象");
            rowTitle6_cellTitle1.setCellStyle(cellBoldStyle);

            Cell rowTitle6__rowTitle2 = rowTitle6.createCell(1);
            rowTitle6__rowTitle2.setCellValue("检查内容");
            rowTitle6__rowTitle2.setCellStyle(cellBoldStyle);
            sheet.addMergedRegion(new CellRangeAddress(6, 6, 1, 10));
            //修补合并后边框不完整的情况
            rowTitle6.createCell(2).setCellStyle(cellStyle);
            rowTitle6.createCell(3).setCellStyle(cellStyle);
            rowTitle6.createCell(4).setCellStyle(cellStyle);
            rowTitle6.createCell(5).setCellStyle(cellStyle);
            rowTitle6.createCell(6).setCellStyle(cellStyle);
            rowTitle6.createCell(7).setCellStyle(cellStyle);
            rowTitle6.createCell(8).setCellStyle(cellStyle);
            rowTitle6.createCell(9).setCellStyle(cellStyle);
            rowTitle6.createCell(10).setCellStyle(cellStyle);



            //第一行合并单元格
            //sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));
            //第一行设置列宽
            //sheet.setColumnWidth(0, (short) (25 * 250));
            //sheet.setColumnWidth(1, (short) (25 * 250));

            //设置行高
            short rowHeight = (short) (25 * 15);
            rowTitle1.setHeight(rowHeight);
            rowTitle4.setHeight(rowHeight);
            rowTitle5.setHeight(rowHeight);
            rowTitle6.setHeight(rowHeight);






            //循环写入设备信息
            //从第几行开始
            int index = 7;
            for (int i = 0; i < SBInfo.size(); i++) {

                //获得已选中的巡检项XJXATID
                String[] checkedXJXAtid = SBInfo.get(i).get("xjxatid").toString().split(",");

                //设置列宽度
                //sheet.setColumnWidth(0, 100);
                String SBATID = SBInfo.get(i).get("sbatid").toString();



                HSSFRow row1 = sheet.createRow(index+i);
                Cell row1_cellContent1 = row1.createCell(0);
                row1_cellContent1.setCellValue(SBInfo.get(i).get("sbname").toString());
                row1_cellContent1.setCellStyle(cellStyle);

                //查询设备设施
                List<Map<String,Object>> SBSSInfo = exportExcelInspectionMapper.byParentIdGetRWZJBInfo(SBATID);

                String Text = "";
                for(int k = 0;k<SBSSInfo.size();k++){

                    String SBSSAtid = SBSSInfo.get(k).get("zjatid").toString();
                    String SBSSName = SBSSInfo.get(k).get("zjname").toString();

                    Text = Text + SBSSName +"：";
                    //查询巡检项
                    List<Map<String,Object>> XJXInfo = exportExcelInspectionMapper.byParentIdGetRWZJBInfo(SBSSAtid);


                    for(int j = 0;j<XJXInfo.size();j++){
                        //获得当前巡检项
                        String XJXAtid = XJXInfo.get(j).get("zjatid").toString();

                        boolean isChecked = false;
                        for (int z = 0;z<checkedXJXAtid.length;z++){
                            if(XJXAtid.equals(checkedXJXAtid[z])){
                                isChecked = true;
                                break;
                            }else{
                                isChecked = false;
                            }
                        }

                        if (isChecked) {
                            Text = Text + XJXInfo.get(j).get("zjname").toString()+"☑ ";
                        }else{
                            Text = Text + XJXInfo.get(j).get("zjname").toString()+"□ ";
                        }

                    }
                    Text = Text+"\n\n";

                }

                Text = Text + "问题描述："+SBInfo.get(i).get("wzms").toString()+"\n";
                Cell row1_cellContent2 = row1.createCell(1);
                row1_cellContent2.setCellValue(Text);
                row1_cellContent2.setCellStyle(cellAutoRowStyle);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(index+i, index+i, 1, 10));

                //修补合并后边框不完整的情况
                row1.createCell(2).setCellStyle(cellStyle);
                row1.createCell(3).setCellStyle(cellStyle);
                row1.createCell(4).setCellStyle(cellStyle);
                row1.createCell(5).setCellStyle(cellStyle);
                row1.createCell(6).setCellStyle(cellStyle);
                row1.createCell(7).setCellStyle(cellStyle);
                row1.createCell(8).setCellStyle(cellStyle);
                row1.createCell(9).setCellStyle(cellStyle);
                row1.createCell(10).setCellStyle(cellStyle);

                //使用单元格合并后 设置自动行高 不合并的话，默认是有自动行高的
                float h = getExcelCellAutoHeight(Text, 13, 33);
                sheet.getRow(index+i).setHeightInPoints(h);

                //获取照片路径
                List<Map<String,Object>> ZPPathList = exportExcelInspectionMapper.bySBATIDGetZp(SBInfo.get(i).get("sbatid").toString());
                if(ZPPathList.size()>0){
                    index = index +1;
                    HSSFRow row2 = sheet.createRow(i+index);
                    Cell row2_cellContent1 = row2.createCell(0);
                    row2_cellContent1.setCellValue("问题照片");
                    row2_cellContent1.setCellStyle(cellStyle);
                    row2.setHeight((short) (25 * 50));

                    row2.createCell(2).setCellStyle(cellStyle);
                    row2.createCell(3).setCellStyle(cellStyle);
                    row2.createCell(4).setCellStyle(cellStyle);
                    row2.createCell(5).setCellStyle(cellStyle);
                    row2.createCell(6).setCellStyle(cellStyle);
                    row2.createCell(7).setCellStyle(cellStyle);
                    row2.createCell(8).setCellStyle(cellStyle);
                    row2.createCell(9).setCellStyle(cellStyle);
                    row2.createCell(10).setCellStyle(cellStyle);

                    for (int j = 0;j<ZPPathList.size();j++){
                        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                        BufferedImage bufferImg = ImageIO.read(new File(ZPPathList.get(j).get("filepath").toString()));
                        ImageIO.write(bufferImg, ZPPathList.get(j).get("linktype").toString(), byteArrayOut);

                        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

                        //anchor主要用于设置图片的属性                                                      图片前面的格子 图片上面格子 图片宽窄   图片下面的格子
                        HSSFClientAnchor anchor = new HSSFClientAnchor(20, 5, 1000, 255,(short) (j+1), i+index, (short) (j+1), i+index);
                        //插入图片
                        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                }



                *//**
                 * 表格模版需要修改
                 *
                 *
                 * 做到图片插入的方式，还面还剩3天
                 *
                 *
                 *
                 * 做到了，插入图片，需要修改模版
                 *//*


            }

            index = index + SBInfo.size()-1;
            HSSFRow rowFooter1 = sheet.createRow(index+1);
            Cell rowFooter1_cellTitle1 = rowFooter1.createCell(0);
            rowFooter1_cellTitle1.setCellValue("检查人1");
            rowFooter1_cellTitle1.setCellStyle(cellBoldStyle);

            Cell rowFooter1_rowTitle2 = rowFooter1.createCell(1);
            rowFooter1_rowTitle2.setCellValue(RWInfo.get("xjfzr").toString());
            rowFooter1_rowTitle2.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(index+1, index+1, 1, 10));
            //修补合并后边框不完整的情况
            rowFooter1.createCell(2).setCellStyle(cellStyle);
            rowFooter1.createCell(3).setCellStyle(cellStyle);
            rowFooter1.createCell(4).setCellStyle(cellStyle);
            rowFooter1.createCell(5).setCellStyle(cellStyle);
            rowFooter1.createCell(6).setCellStyle(cellStyle);
            rowFooter1.createCell(7).setCellStyle(cellStyle);
            rowFooter1.createCell(8).setCellStyle(cellStyle);
            rowFooter1.createCell(9).setCellStyle(cellStyle);
            rowFooter1.createCell(10).setCellStyle(cellStyle);

            HSSFRow rowFooter2 = sheet.createRow(index+2);
            Cell rowFooter2_cellTitle1 = rowFooter2.createCell(0);
            rowFooter2_cellTitle1.setCellValue("检查人2");
            rowFooter2_cellTitle1.setCellStyle(cellBoldStyle);

            Cell rowFooter2_rowTitle2 = rowFooter2.createCell(1);
            rowFooter2_rowTitle2.setCellValue(RWInfo.get("xjjcr").toString());
            rowFooter2_rowTitle2.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(index+2, index+2, 1, 10));
            //修补合并后边框不完整的情况
            rowFooter2.createCell(2).setCellStyle(cellStyle);
            rowFooter2.createCell(3).setCellStyle(cellStyle);
            rowFooter2.createCell(4).setCellStyle(cellStyle);
            rowFooter2.createCell(5).setCellStyle(cellStyle);
            rowFooter2.createCell(6).setCellStyle(cellStyle);
            rowFooter2.createCell(7).setCellStyle(cellStyle);
            rowFooter2.createCell(8).setCellStyle(cellStyle);
            rowFooter2.createCell(9).setCellStyle(cellStyle);
            rowFooter2.createCell(10).setCellStyle(cellStyle);

            HSSFRow rowFooter3 = sheet.createRow(index+3);
            Cell rowFooter3_cellTitle1 = rowFooter3.createCell(0);
            rowFooter3_cellTitle1.setCellValue("主管领导");
            rowFooter3_cellTitle1.setCellStyle(cellBoldStyle);

            Cell rowFooter3_rowTitle2 = rowFooter3.createCell(1);
            rowFooter3_rowTitle2.setCellValue("");
            rowFooter3_rowTitle2.setCellStyle(cellStyle);

            sheet.addMergedRegion(new CellRangeAddress(index+3, index+3, 1, 13));
            //修补合并后边框不完整的情况
            rowFooter3.createCell(2).setCellStyle(cellStyle);
            rowFooter3.createCell(3).setCellStyle(cellStyle);
            rowFooter3.createCell(4).setCellStyle(cellStyle);
            rowFooter3.createCell(5).setCellStyle(cellStyle);
            rowFooter3.createCell(6).setCellStyle(cellStyle);
            rowFooter3.createCell(7).setCellStyle(cellStyle);
            rowFooter3.createCell(8).setCellStyle(cellStyle);
            rowFooter3.createCell(9).setCellStyle(cellStyle);
            rowFooter3.createCell(10).setCellStyle(cellStyle);

            //修补合并后边框不完整的情况
            //for (Cell cell : rowFooter3) {
            //    cell.setCellStyle(cellStyle);
            //}


            //设置最后三行行高
            rowFooter1.setHeight(rowHeight);
            rowFooter2.setHeight(rowHeight);
            rowFooter3.setHeight(rowHeight);




            *//********************浏览器下载文件******************//*
            // 输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();

            String fileName = "运管系统_导出巡检结果表单_任务名称："+RWInfo.get("rwmc")+".xls";
            // 设置文件头
            response.setHeader("Content-Disposition",
                    "attchement;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));

            response.setContentType("application/msexcel");
            wb.write(output);
            wb.close();

        } catch (Exception e) {
            e.printStackTrace();

        }*/

    }

    // 自动调整行高
    public static float getExcelCellAutoHeight(String str, float defaultRowHeight, int fontCountInline) {
        int defaultCount = 0;

        for (int i = 0; i < str.length(); i++) {
            int ff = 1;
            String charStr = str.substring(i, i + 1);
            if (charStr == " ") {
                ff = 1;
            }
            // 判断是否为字母或字符
            if (Pattern.compile("^[A-Za-z0-9]+$").matcher(charStr).matches()) {
                ff = 1;
            }
            // 判断是否为全角

            if (Pattern.compile("[\u4e00-\u9fa5]+$").matcher(charStr).matches()) {
                ff = 2;
            }
            // 全角符号 及中文
            if (Pattern.compile("[^x00-xff]").matcher(charStr).matches()) {
                ff = 2;
            }
            defaultCount = defaultCount + ff;
        }
        if (defaultCount > fontCountInline) {
            return ((int) (defaultCount / fontCountInline) + 1) * defaultRowHeight;// 计算
        } else {
            return defaultRowHeight;
        }
    }

    /**
    * 合并单元格
    * @author tongyao
    * @param sheet sheet页
    * @param titleColumn 标题占用行
    * @param cellIndex 那个单元格
    */
    public static void mergeCell(HSSFSheet sheet,int titleColumn,int cellIndex){
        //多少行
        int rowCount = sheet.getPhysicalNumberOfRows();
        String cellText = "";
        //开始下标
        int startIndex = 0;
        //结束下标
        int endIndex = 0;
        HSSFRow row = null;
        Cell cell = null;
        for(int i = titleColumn;i<rowCount;i++){
            row = sheet.getRow(i);
            cell = row.getCell(cellIndex);

            if(!cell.getStringCellValue().equals(cellText)){
                if(startIndex != 0){
                    endIndex = (i-1);
                    sheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, cellIndex, cellIndex));
                }
                cellText = cell.getStringCellValue();
                startIndex = i;
            }

        }

        endIndex = (rowCount-1);
        sheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, cellIndex, cellIndex));
    }



    //导入表格
    @PostMapping("/importExcel")
    public Object importExcel(MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception {
        //String templatePath = "C:\\file_template\\test.xls";
        //HSSFWorkbook book = new HSSFWorkbook(new FileInputStream(ResourceUtils.getFile(templatePath)));

        HSSFWorkbook book = new HSSFWorkbook(file.getInputStream());
        
        int sheetCount = book.getActiveSheetIndex();
        System.out.println("本表格sheet页数量："+sheetCount+1);
        
        HSSFSheet sheet = book.getSheetAt(0);

        HSSFRow row = null;
        for (int i = 0; i < sheet.getLastRowNum()+1; i++) {
            row = sheet.getRow(i);

            HSSFCell row_cell1 = row.getCell(0);
            //设置单元格类型，允许使用数字
            row_cell1.setCellType(CellType.STRING);

            HSSFCell row_cell2 = row.getCell(1);
            row_cell2.setCellType(CellType.STRING);

            HSSFCell row_cell3 = row.getCell(2);
            row_cell3.setCellType(CellType.STRING);


            System.out.println(row_cell1.getStringCellValue()+"\t\t"
                    +row_cell2.getStringCellValue()+"\t\t"
                    +row_cell3.getStringCellValue());
        }
        book.close();
        return null;
    }

    /**
     * 导出文档接口
     * @param response
     */
    @GetMapping("/exportWord")
    public void exportWord(HttpServletResponse response){

        //替换你的word里面的${title}关键字
        Map<String, String> wordData = new HashMap<String, String>();
        wordData.put("title", "个人信息简介");
        wordData.put("name", "张三");
        wordData.put("sex", "男");
        wordData.put("info", "我是个很帅的人~");

        String tmpFile = new ExportController().getResourcesPath()+ "/file_template/wordtest.doc";

        //注意：模版word文件只支持.doc文档
        new ExportController().buildWord(response,tmpFile,wordData,"导出文档.doc");

    }

    /**
     * 导出文档
     * @param response
     * @param templatePath
     * @param wordData
     * @param fileName
     */
    public void buildWord(HttpServletResponse response,
                          String templatePath,
                          Map<String, String> wordData,
                          String fileName){


        HWPFDocument document = null;
        try {
            FileInputStream tempFileInputStream = new FileInputStream(templatePath);
            document = new HWPFDocument(tempFileInputStream);

            // 读取并替换文本内容
            Range bodyRange = document.getRange();
            for (Map.Entry<String, String> entry : wordData.entrySet()) {
                bodyRange.replaceText("${" + entry.getKey() + "}", entry.getValue());
            }
        } catch (IOException e) {
            e.printStackTrace();
        }


        /********************浏览器下载文件******************/
        try {
            // 输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();

            // 设置文件头
            response.setHeader("Content-Disposition",
                    "attchement;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));

            response.setContentType("application/msexcel");
            document.write(output);

        } catch (IOException e) {
            e.printStackTrace();
        }

        /********************本地下载***********************/
        /*try {
            String fileName = "D:\\exportFrom\\"+new Date().getTime() +".doc";

            FileOutputStream fileOutputStream = new FileOutputStream(fileName);
            // 写入word文件
            document.write(fileOutputStream);
            fileOutputStream.close();
            System.out.println("----doc文件已生成------");

            java.awt.Desktop.getDesktop().open(new File(fileName));
            System.exit(0);
        } catch (IOException e) {
            e.printStackTrace();
        }*/

    }

    // 图片资源可以用于存放于项目根目录，没必要说非得放到资源目录下
    public String getResourcesPath(){
        return new File(this.getClass().getResource("/").getPath()).toString();
    }

    static {
        System.setProperty("java.awt.headless", "false");
    }

    /**
     * 导出详细文档接口
     * @param response
     */
    @GetMapping("/exportWord2")
    public void exportWord2(HttpServletResponse response) throws Exception{

        // 1、创建word文档对象
        /*InputStream inputStream=new FileInputStream("C:\\Users\\zhang\\Desktop\\巡检报告导出模板.docx");
        XWPFDocument document = new XWPFDocument(inputStream);*/

        XWPFDocument document = new XWPFDocument();

        // 创建段落对象
        XWPFParagraph xwpfParagraph = null;
        // 创建字体对象
        XWPFRun xwpfRun = null;

        // 将页边距设置为1.27厘米
        PoiUtils.setPageMargin(document, 724, 724, 724, 724);

        /************************设置页眉开始****************************/
        CTP ctp = CTP.Factory.newInstance();
        XWPFParagraph paragraph = new XWPFParagraph(ctp, document);
        ctp.addNewR().addNewT().setStringValue("我是页眉！~");
        ctp.addNewR().addNewT().setSpace(SpaceAttribute.Space.PRESERVE);
        CTSectPr sectPr = document.getDocument().getBody().isSetSectPr() ? document.getDocument().getBody().getSectPr() : document.getDocument().getBody().addNewSectPr();
        XWPFHeaderFooterPolicy policy = new XWPFHeaderFooterPolicy(document, sectPr);
        XWPFHeader header = policy.createHeader(STHdrFtr.DEFAULT, new XWPFParagraph[] { paragraph });
        header.setXWPFDocument(document);



        /************************创建表格开始****************************/
        List<String> tableHead = new ArrayList<>();
        tableHead.add("我是标题1");
        tableHead.add("我是标题2");
        tableHead.add("我是标题3");
        tableHead.add("我是标题4");
        tableHead.add("我是标题5");
        tableHead.add("我是标题6");
        tableHead.add("我是标题7");
        List<List<Object>> row = new ArrayList<>();

        List<Object> cell = new ArrayList<>();
        cell.add("我是内容1");
        cell.add("我是内容2");
        cell.add("我是内容3");
        cell.add("我是内容4");
        cell.add("我是内容5");
        cell.add("我是内容6");
        cell.add("我是内容7");
        row.add(cell);

        PoiUtils.createTable(
                document, 7, "bebebe", 800, 500, tableHead, row);
        PoiUtils.addNewLine(document,1);



        /************************创建折线图开始****************************/

        PoiUtils.createLineChart(
                document,
                "曲线测试",
                "按小时统计",
                "比例值",
                new String[] {
                        "00时","01时","02时","03时","04时","05时",
                        "06时","07时","08时","09时","10时","11时",
                        "12时","13时","14时","15时","16时","17时",
                        "18时","19时","20时","21时","22时","23时",
                },new Integer[]{
                        10, 35, 21, 46, 79, 88,
                        39, 102, 71, 28, 99, 57,
                        0, 0, 0, 0, 0, 0,
                        0, 0, 0, 0, 0, 0,
                });
        PoiUtils.addNewLine(document,5);

        String fileName = "D:\\worddoc\\lineChart"+System.currentTimeMillis()+".docx";

        // 转成pdf

        FileOutputStream fos = new FileOutputStream(fileName);
        document.write(fos); // 导出word
        System.out.println("----文件已生成------");
        java.awt.Desktop.getDesktop().open(new File(fileName));
        fos.close();
        document.close();

        java.awt.Desktop.getDesktop().open(new File(file2pdf(fileName)));
        System.exit(0);
    }



    /**
     * @param toFilePath 文件夹路径 word转pdf
     */
    public static String file2pdf(String toFilePath) throws Exception {
        String type = toFilePath.substring(toFilePath.lastIndexOf(".") + 1);
        String htmFileName;
        //获取转换成PDF之后文件名
        if ("doc".equals(type)) {
            htmFileName = toFilePath.replace("doc", "pdf");
        } else if ("docx".equals(type)) {
            htmFileName = toFilePath.replace("docx", "pdf");
        } else {
            return null;
        }
        // 全面支持DOC, DOCX, OOXML, RTF HTML, OpenDocument, PDF, EPUB, XPS, SWF 相互转换
        //通过转换之后的PDF文件名,创建PDF文件
        File htmlOutputFile = new File(htmFileName);
        //获取文件输出流
        FileOutputStream os = new FileOutputStream(htmlOutputFile);
        //获取Doc文档对象模型
        Document doc = new Document(toFilePath);
        DocumentBuilder builder = new DocumentBuilder(doc);
        builder.setBold(false);
        //         设置纸张大小
//        builder.getPageSetup().setPaperSize(PaperSize.A3);

        //为doc文档添加水印
        //insertWatermarkText(doc, "test效果");
        //将doc文旦转换成PDF文件并输出到之前创建好的pdf文件中
        doc.save(os, SaveFormat.PDF);
        //关闭输出流
        os.close();
        return htmFileName;
    }



    /*读取外部xls表格文件*/
    public static void main(String[] args) throws Exception{
        /*Workbook workbook = Workbook.getWorkbook(file.getInputStream());*/
        Workbook workbook = Workbook.getWorkbook(new File("C:\\Users\\zhang\\Desktop\\xxx.xls"));
        // 暂时不需要多个sheet，1个就行
        Sheet sheet = workbook.getSheet(0);
        for (int i = 0; i < sheet.getRows()-1; i++) {
            String aaa = "";
            for (int j = 0; j < sheet.getColumns()-1; j++) {
                aaa+=sheet.getCell(j,i).getContents()+"\t";
            }
            System.out.println(aaa);
        }
    }


}
